using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using webapp4U.BOL;

namespace webapp4U.DAL
{
    public class ArticleDAL : BaseDAL
    {

        public string GetImageURLByArticleID(int ArticleID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                try
                {
                    string value = "";
                    SqlCommand myCommand = new SqlCommand("Article_GetImageURLByArticleID", myConn);
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.Add("@ArticleID", SqlDbType.Int).Value = ArticleID;
                    value = (string)myCommand.ExecuteScalar();
                    myConn.Close();
                    return value;
                }
                catch { return ""; }
            }

        }


        public List<Article> GetArticleMenu(int ArticleCateID)
        {
            //Article_GetArticleHomePage
            using (SqlConnection myConn = GetConnectionString())
            {
                string sql = "select top 7 * from Article Where(ArticleID>0)";
                sql = sql + " And (Priority = 1) ";
                sql = sql + " Order by ArticleID Desc";
                SqlCommand myCommand = new SqlCommand(sql, myConn);
                myCommand.CommandType = CommandType.Text;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetArticleCollectionFromReader(dr);
            }

        }

        public string GetImage1ByArticleID(int ArticleID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                try
                {
                    string value = "";
                    SqlCommand myCommand = new SqlCommand("select Img1 from Article where ArticleID="+ ArticleID, myConn);
                    myCommand.CommandType = CommandType.Text;
                    value = (string)myCommand.ExecuteScalar();
                    myConn.Close();
                    return value;
                }
                catch { return ""; }
            }

        }


        public string GetImage2ByArticleID(int ArticleID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                try
                {
                    string value = "";
                    SqlCommand myCommand = new SqlCommand("select Img2 from Article where ArticleID=" + ArticleID, myConn);
                    myCommand.CommandType = CommandType.Text;
                    value = (string)myCommand.ExecuteScalar();
                    myConn.Close();
                    return value;
                }
                catch { return ""; }
            }

        }

        public List<Article> GetTuVan(int CateID)
        {
            //Article_GetArticleHomePage
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Article_GetTuVan", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetArticleCollectionFromReader(dr);
            }
        }



        public List<Article> GetArticleHomePage(int ArticleCateID)
        {
            //Article_GetArticleHomePage
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Article_GetArticleHomePage", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ArticleCateID", SqlDbType.Int).Value = ArticleCateID;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetArticleCollectionFromReader(dr);
            }

        }

        public int GetArticleCateIDByArticleID(int ArticleID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                try
                {
                    int value = 0;
                    SqlCommand myCommand = new SqlCommand("select ArticleCateID from Article Where ArticleID=@ArticleID", myConn);
                    myCommand.CommandType = CommandType.Text;
                    myCommand.Parameters.Add("@ArticleID", SqlDbType.Int).Value = ArticleID;
                    value = (int)myCommand.ExecuteScalar();
                    myConn.Close();
                    return value;
                }
                catch { return 0; }
            }

        }


        public List<Article> Search(string Keyword, int CateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                List<Article> list = new List<Article>();
                string sql = "select Article.* From [Article],[ArticleCategory] ";
                sql = sql + " Where (Article.ArticleCateID = ArticleCategory.CateID)";
                if (CateID > 1)
                {
                    sql = sql + "And (Article.ArticleCateID = " + CateID + ")";
                }
                if (Keyword.Length > 1)
                {
                    sql = sql + "And((Title Like '%" + Keyword + "%') Or (Excerpt Like '%" + Keyword + "%') ) ";
                }
                sql = sql + " Order By Priority Desc, ArticleID Desc";

            
                SqlCommand myCommand = new SqlCommand(sql, myConn);
                myCommand.CommandType = CommandType.Text;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(SearchArticleIDataReader(dr,Keyword));
                    }
                    dr.Close();
                    myConn.Close();
                }
                return list;
            }
        }

        private Article SearchArticleIDataReader(IDataReader dr, string kw)
        {
            Article obj = new Article();
            obj.ArticleID = (dr["ArticleID"] is DBNull) ? int.MinValue : (int)dr["ArticleID"];
            obj.Title = (dr["Title"] is DBNull) ? string.Empty : (string)dr["Title"].ToString().Replace(kw,"<span  style='background-color:Yellow'>"+  kw +"</span>").Replace("<span  style='background-color:Yellow'> </span>"," ");
            obj.Excerpt = (dr["Excerpt"] is DBNull) ? string.Empty : (string)dr["Excerpt"].ToString().Replace(kw, "<span  style='background-color:Yellow'>" + kw + "</span>");
            obj.Body = (dr["Body"] is DBNull) ? string.Empty : (string)dr["Body"].ToString().Replace("/demo",Globals.ApplicationPath);
            obj.ImageURL = (dr["ImageURL"] is DBNull) ? string.Empty : (string)dr["ImageURL"];
            obj.ImageWidth = (dr["ImageWidth"] is DBNull) ? int.MinValue : (int)dr["ImageWidth"];
            obj.ImageHeight = (dr["ImageHeight"] is DBNull) ? int.MinValue : (int)dr["ImageHeight"];
            obj.Priority = (dr["Priority"] is DBNull) ? int.MinValue : (int)dr["Priority"];
            obj.Authors = (dr["Authors"] is DBNull) ? string.Empty : (string)dr["Authors"];
            obj.PostDate = (dr["PostDate"] is DBNull) ? DateTime.MinValue : (DateTime)dr["PostDate"];
            obj.TotalViews = (dr["TotalViews"] is DBNull) ? int.MinValue : (int)dr["TotalViews"];
            obj.ArticleCateID = (dr["ArticleCateID"] is DBNull) ? int.MinValue : (int)dr["ArticleCateID"];
            return obj;
        }


        public List<Article> GetArticleRecent(Int32 ArticleCateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("webapp4U_Article_GetArticleRecent", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ArticleCateID", SqlDbType.Int).Value = ArticleCateID;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetArticleCollectionFromReader(dr);
            }
        }


        //Get GetArticleByParentID
        public List<Article> GetArticleByParentID(int ParentID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Article_GetArticleByParentID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = ParentID;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetArticleCollectionFromReader(dr);
            }
        }


        //Get All Article
        public List<Article> GetAllArticle(int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Article_GetAllArticle", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetArticleCollectionFromReader(dr);
            }
        }

        // Get Count All records of Table  Article
        public int GetCountArticle()
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Article_GetCountArticle", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                return (int)myCommand.ExecuteScalar();
            }
        }

        //Lay nhung tin tuc de hien thi tren trang chu
        public List<Article> GetArticleHomeByParentArticleCateID(int ParentArticleCateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                List<Article> list = new List<Article>();
                SqlCommand myCommand = new SqlCommand("Article_GetArticleHomeByParentArticleCateID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ParentArticleCateID", SqlDbType.Int).Value = ParentArticleCateID;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(ArticleIDataReader(dr));
                    }
                    dr.Close();
                    myConn.Close();
                }
                return list;
            }

        }
        
        //Lay nhung tin tuc nhieu nguoi xem nhat de hien thi tren menu
        public List<Article> GetArticleTopTotalviews(int ArticleCateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                List<Article> list = new List<Article>();
                SqlCommand myCommand = new SqlCommand("Article_GetArticleTopTotalviews", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ParentArticleCateID", SqlDbType.Int).Value = ArticleCateID;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(ArticleIDataReader(dr));
                    }
                    dr.Close();
                    myConn.Close();
                }
                return list;
            }

        }


        public List<Article> GetTop2ArticleByArticleCateID(int ArticleCateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                List<Article> list = new List<Article>();
                SqlCommand myCommand = new SqlCommand("Article_GetTop2ArticleByArticleCateID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ArticleCateID", SqlDbType.Int).Value = ArticleCateID;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(ArticleIDataReader(dr));
                    }
                    dr.Close();
                    myConn.Close();
                }
                return list;
            }

        }


        public List<Article> GetTop10ArticleByArticleCateID(int ArticleCateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                List<Article> list = new List<Article>();
                SqlCommand myCommand = new SqlCommand("Article_GetTop10ArticleByArticleCateID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ArticleCateID", SqlDbType.Int).Value = ArticleCateID;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(ArticleIDataReader(dr));
                    }
                    dr.Close();
                    myConn.Close();
                }
                return list;
            }

        }


        public List<Article> GetArticleByArticleCateID(int ArticleCateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                List<Article> list = new List<Article>();
                SqlCommand myCommand = new SqlCommand("Article_GetArticleByArticleCateID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ArticleCateID", SqlDbType.Int).Value = ArticleCateID;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(ArticleIDataReader(dr));
                    }
                    dr.Close();
                    myConn.Close();
                }
                return list;
            }        

        }


        public Article GetArticleByArticleID(int ArticleID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                Article obj = new Article();
                SqlCommand myCommand = new SqlCommand("Article_GetArticleByArticleID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ArticleID", SqlDbType.Int).Value = ArticleID;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        obj = ArticleIDataReader(dr);
                    }
                    dr.Close();
                    myConn.Close();
                }
                return obj;
            }     
        }


        public List<Article> GetArticleOtherArticleID(int ArticleID, int ArticleCateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                List<Article> list = new List<Article>();
                SqlCommand myCommand = new SqlCommand("Article_GetArticleOtherArticleID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ArticleID", SqlDbType.Int).Value = ArticleID;
                myCommand.Parameters.Add("@ArticleCateID", SqlDbType.Int).Value = ArticleCateID;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(ArticleIDataReader(dr));
                    }
                    dr.Close();
                    myConn.Close();
                }
                return list;
            }        

        }

        private  Article ArticleIDataReader(IDataReader dr)
        {
            Article obj = new Article();
            obj.ArticleID = (dr["ArticleID"] is DBNull) ? int.MinValue : (int)dr["ArticleID"];
            obj.Title = (dr["Title"] is DBNull) ? string.Empty : (string)dr["Title"];
            obj.Excerpt = (dr["Excerpt"] is DBNull) ? string.Empty : (string)dr["Excerpt"];
            obj.Body = (dr["Body"] is DBNull) ? string.Empty : (string)dr["Body"].ToString().Replace("/demo", Globals.ApplicationPath);
            obj.ImageURL = (dr["ImageURL"] is DBNull) ? string.Empty : (string)dr["ImageURL"];
            obj.ImageWidth = (dr["ImageWidth"] is DBNull) ? int.MinValue : (int)dr["ImageWidth"];
            obj.ImageHeight = (dr["ImageHeight"] is DBNull) ? int.MinValue : (int)dr["ImageHeight"];
            obj.Priority = (dr["Priority"] is DBNull) ? int.MinValue : (int)dr["Priority"];
            obj.Authors = (dr["Authors"] is DBNull) ? string.Empty : (string)dr["Authors"];
            obj.PostDate = (dr["PostDate"] is DBNull) ? DateTime.MinValue : (DateTime)dr["PostDate"];
            obj.TotalViews = (dr["TotalViews"] is DBNull) ? int.MinValue : (int)dr["TotalViews"];
            obj.ArticleCateID = (dr["ArticleCateID"] is DBNull) ? int.MinValue : (int)dr["ArticleCateID"];
            return obj;
        }
        
        /// <summary>
        /// Returns a collection of Article with the data read from the input DataReader
        /// </summary>
        private List<Article> GetArticleCollectionFromReader(IDataReader dr)
        {
            List<Article> obj = new List<Article>();
            while (dr.Read())
                obj.Add(ArticleIDataReader(dr));
            return obj;
        }


        public int UpdateTotalViews(int ArticleID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Update Article set TotalViews=TotalViews + 1 Where ArticleID=@ArticleID", myConn);
                myCommand.CommandType = CommandType.Text;
                myCommand.Parameters.Add("@ArticleID", SqlDbType.Int).Value = ArticleID;
                myCommand.ExecuteNonQuery();
                myConn.Close();
                return 1;
            }
        }
        
        public int Insert(Article obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Article_Insert", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
                myCommand.Parameters.Add("@Excerpt", SqlDbType.NVarChar).Value = obj.Excerpt;
                myCommand.Parameters.Add("@Body", SqlDbType.NVarChar).Value= obj.Body;
                myCommand.Parameters.Add("@ImageURL",SqlDbType.NVarChar).Value= obj.ImageURL;
                myCommand.Parameters.Add("@ImageWidth",SqlDbType.Int).Value= obj.ImageWidth;
                myCommand.Parameters.Add("@ImageHeight", SqlDbType.Int).Value= obj.ImageHeight;
                myCommand.Parameters.Add("@Priority",  SqlDbType.Int).Value= obj.Priority;
                myCommand.Parameters.Add("@Authors", SqlDbType.NVarChar).Value= obj.Authors;
                myCommand.Parameters.Add("@ArticleCateID", SqlDbType.Int).Value= obj.ArticleCateID;
                myCommand.ExecuteNonQuery();
                myConn.Close();
                return 1;
            }
        }

        public int Update(Article obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Article_Update", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
                myCommand.Parameters.Add("@Excerpt", SqlDbType.NVarChar).Value = obj.Excerpt;
                myCommand.Parameters.Add("@Body", SqlDbType.NVarChar).Value = obj.Body;
                myCommand.Parameters.Add("@ImageURL", SqlDbType.NVarChar).Value = obj.ImageURL;
                myCommand.Parameters.Add("@ImageWidth", SqlDbType.Int).Value = obj.ImageWidth;
                myCommand.Parameters.Add("@ImageHeight", SqlDbType.Int).Value = obj.ImageHeight;
                myCommand.Parameters.Add("@Priority", SqlDbType.Int).Value = obj.Priority;
                myCommand.Parameters.Add("@Authors", SqlDbType.NVarChar).Value = obj.Authors;
                myCommand.Parameters.Add("@ArticleCateID", SqlDbType.Int).Value = obj.ArticleCateID;
                myCommand.Parameters.Add("@ArticleID", SqlDbType.Int).Value = obj.ArticleID;
                myCommand.ExecuteNonQuery();
                myConn.Close();
                return 1;
            }
        }

        public int Delete(int ArticleID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Article_Delete", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ArticleID", SqlDbType.Int).Value = ArticleID;
                myCommand.ExecuteNonQuery();
                myConn.Close();
                return 1;
            }
        }


	}

}
